
package cn.terenas.tool.excel;

import cn.terenas.tool.excel.common.constant.Constant;
import cn.terenas.tool.excel.common.dto.data.ExtractedData;
import cn.terenas.tool.excel.common.dto.data.Transfer;
import cn.terenas.tool.excel.common.dto.result.AirportStatisticalResult;
import cn.terenas.tool.excel.common.dto.result.RankResult;
import cn.terenas.tool.excel.common.dto.result.StatisticsResult;
import cn.terenas.tool.excel.common.dto.result.UnionStatisticalResult;
import cn.terenas.tool.excel.common.enums.result.AirportTypeEnum;
import cn.terenas.tool.excel.common.enums.result.TransferTypeEnum;
import cn.terenas.tool.excel.config.Config;
import cn.terenas.tool.excel.extractor.Extractor;
import cn.terenas.tool.excel.util.CalcUtil;
import cn.terenas.tool.excel.util.CellUtil;
import com.google.common.collect.Lists;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.tuple.MutablePair;
import org.apache.commons.lang3.tuple.Pair;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import javax.imageio.ImageIO;
import java.awt.Color;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.List;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.concurrent.atomic.AtomicReference;
import java.util.function.Function;
import java.util.stream.Collectors;


@Slf4j
public class Main {
    private static XSSFWorkbook WORKBOOK = null;

    public static void main(String[] args) {
        try (XSSFWorkbook workbook = new XSSFWorkbook()) {
            WORKBOOK = workbook;
            Config.loadConfig();

            LinkedList<ExtractedData> extractedDataList = Extractor.getInstance().extract();

            process(buildDataList(extractedDataList));
        } catch (Exception e) {
            log.debug(">>> 运行异常:", e);
            log.error(">>> 运行异常, 请联系管理员:{}", e.getMessage());
        }
    }

    private static LinkedList<ExtractedData> buildDataList(LinkedList<ExtractedData> extractedDataList) {
        extractedDataList.addFirst(
                new ExtractedData(
                        "地服中转数据分类统计",
                        extractedDataList.stream()
                                .map(ExtractedData::getTransferList)
                                .flatMap(Collection::stream)
                                .collect(Collectors.toList())
                )
        );
        return extractedDataList;
    }

    private static void process(List<ExtractedData> list) {
        log.info(Constant.SEPARATE_LINE);
        log.info(Constant.SEPARATE_LINE);
        log.info(">>> 开始计算并写入数据...");
        log.info(Constant.SEPARATE_LINE);
        log.info(Constant.SEPARATE_LINE + "\n");

        try (FileOutputStream fileOutputStream = new FileOutputStream("./result.xlsx")) {

            AtomicInteger sheetNum = new AtomicInteger();
            list.forEach(extractedData -> processSheet(extractedData, sheetNum));

            WORKBOOK.write(fileOutputStream);

            log.info(Constant.SEPARATE_LINE);
            log.info(Constant.SEPARATE_LINE);
            log.info(">>> 数据写入完成.");
            log.info(Constant.SEPARATE_LINE);
            log.info(Constant.SEPARATE_LINE + "\n");

        } catch (Exception e) {
            log.debug(">>> 数据写入错误, e:", e);
            log.error(">>> 数据写入错误, 请联系管理员!");
        }
    }

    private static void processSheet(ExtractedData extractedData, AtomicInteger sheetNum) {
        try {
            log.info(Constant.SEPARATE_LINE);
            log.info(">>> 正在计算\"{}\"页面数据...", extractedData.getGroupName());

            XSSFSheet sheet = WORKBOOK.createSheet(extractedData.getGroupName());

            initTitleArea(sheet);

            processTotalArea(sheet, extractedData);

            processAirportArea(sheet, extractedData.getTransferList());

            processUnionArea(sheet, extractedData.getTransferList());

            processRankArea(sheet, extractedData.getTransferList());

            sheetNum.incrementAndGet();
            log.info(">>> \"{}\"页面数据处理完成...", extractedData.getGroupName());

        } catch (Exception e) {
            WORKBOOK.removeSheetAt(sheetNum.get());
            log.debug(">>> \"{}\"页面数据处理失败, e:", extractedData.getGroupName(), e);
            log.info(">>> \"{}\"页面数据处理失败, 请联系管理员!", extractedData.getGroupName());
            throw new RuntimeException("\"" + extractedData.getGroupName() + "\"页面数据处理失败!");
        }
    }

    private static void initTitleArea(XSSFSheet sheet) {
        initSheetTitle(sheet);
        importImage(sheet);
        initSignArea(sheet);
        initTotalAreaTitle(sheet);
        initRankAreaTitle(sheet, TransferTypeEnum.DD);
        initRankAreaTitle(sheet, TransferTypeEnum.DI);
        initRankAreaTitle(sheet, TransferTypeEnum.ID);
        initRankAreaTitle(sheet, TransferTypeEnum.II);
        initDomesticAreaTitle(sheet);
        initInternationalAreaTitle(sheet);
        initUnionAreaTitle(sheet);
    }

    private static void initSheetTitle(XSSFSheet sheet) {
        sheet.createRow(0).setHeight(Constant.TITLE_ROW_HEIGHT);

        CellUtil.initCellValue(
                sheet, Constant.TITLE_ROW, Constant.TITLE_COL, "地服中转数据分类统计"
        ).setCellStyle(createTitleCellStyle());

        CellUtil.mergeRegion(
                sheet,
                Constant.TITLE_ROW, Constant.TITLE_ROW,
                Constant.TITLE_COL, Constant.TITLE_COL + Constant.TITLE_WIDTH - 1
        );
    }

    private static void initSignArea(XSSFSheet sheet) {

        CellUtil.initCellValue(
                sheet, Constant.SIGN_AREA_ROW, Constant.SIGN_AREA_COL, "如遇问题，欢迎咨询："
        ).setCellStyle(createSignAreaTopCellStyle());
        CellUtil.mergeRegion(
                sheet,
                Constant.SIGN_AREA_ROW, Constant.SIGN_AREA_ROW,
                Constant.SIGN_AREA_COL, Constant.SIGN_AREA_COL + Constant.SIGN_AREA_WIDTH - 1
        );

        CellUtil.initCellValue(
                sheet, Constant.SIGN_AREA_ROW + 1, Constant.SIGN_AREA_COL, "贾浩 jiahao@ceair.com"
        ).setCellStyle(createSignAreaMiddleCellStyle());
        CellUtil.mergeRegion(
                sheet,
                Constant.SIGN_AREA_ROW + 1, Constant.SIGN_AREA_ROW + 1,
                Constant.SIGN_AREA_COL, Constant.SIGN_AREA_COL + Constant.SIGN_AREA_WIDTH - 1
        );

        CellUtil.initCellValue(
                sheet, Constant.SIGN_AREA_ROW + 2, Constant.SIGN_AREA_COL, "吕一樵 lvyiqiao@ceair.com"
        ).setCellStyle(createSignAreaBottomCellStyle());
        CellUtil.mergeRegion(
                sheet,
                Constant.SIGN_AREA_ROW + 2, Constant.SIGN_AREA_ROW + 2,
                Constant.SIGN_AREA_COL, Constant.SIGN_AREA_COL + Constant.SIGN_AREA_WIDTH - 1
        );

    }

    private static void importImage(XSSFSheet sheet) {
        ///:~ TODO
        String pathname = "img/logo.png";
        try (
                InputStream in = Main.class.getClassLoader().getResourceAsStream(pathname);
                ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        ) {

            // 先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
            ///:~ TODO
            BufferedImage bufferImg = ImageIO.read(in);
            ImageIO.write(bufferImg, "png", byteArrayOutputStream);

            // anchor主要用于设置图片的属性
            XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 255, 255, 0, 0, 5, 1);
            anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);

            // 添加图片.
            int pictureIndex = WORKBOOK.addPicture(byteArrayOutputStream.toByteArray(), XSSFWorkbook.PICTURE_TYPE_PNG);

            // 画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
            XSSFDrawing drawingPatriarch = sheet.createDrawingPatriarch();
            // 插入图片.
            drawingPatriarch.createPicture(anchor, pictureIndex);

        } catch (Exception e) {
            log.debug(">>> 图片导入失败, e:", e);
            log.error(">>> 图片导入失败, 请联系管理员!");
        }
    }

    private static void initTotalAreaTitle(XSSFSheet sheet) {
        CellUtil.initCellValue(
                sheet, Constant.TOTAL_AREA_ROW, Constant.TOTAL_AREA_COL, "分类统计（总表）"
        ).setCellStyle(createTotalAreaTitleCellStyle());
        CellUtil.mergeRegion(
                sheet,
                Constant.TOTAL_AREA_ROW, Constant.TOTAL_AREA_ROW,
                Constant.TOTAL_AREA_COL, Constant.TOTAL_AREA_COL + Constant.TOTAL_AREA_WIDTH - 1
        );

        CellUtil.initCellValue(
                sheet, Constant.TOTAL_AREA_ROW + 1, Constant.TOTAL_AREA_COL, ""
        ).setCellStyle(getDataTitleCellStyle());
        initCalcDataTitle(sheet, Constant.TOTAL_AREA_ROW + 1, Constant.TOTAL_AREA_COL + 1);
    }

    private static void initRankAreaTitle(XSSFSheet sheet, TransferTypeEnum transferTypeEnum) {

        final int rankAreaRow = Constant.RANK_AREA_ROW_MAP.get(transferTypeEnum.name());
        CellUtil.initCellValue(
                sheet, rankAreaRow, Constant.RANK_AREA_COL, "流向Top10（" + transferTypeEnum.name() + "）"
        ).setCellStyle(createRankAreaTitleCellStyle());
        CellUtil.mergeRegion(
                sheet,
                rankAreaRow, rankAreaRow,
                Constant.RANK_AREA_COL, Constant.RANK_AREA_COL + Constant.RANK_AREA_WIDTH - 1
        );

        CellUtil.initCellValue(
                sheet, rankAreaRow + 1, Constant.RANK_AREA_COL, "排名"
        ).setCellStyle(getDataTitleCellStyle());

        CellUtil.initCellValue(
                sheet, rankAreaRow + 1, Constant.RANK_AREA_COL + 1, "航线"
        ).setCellStyle(getDataTitleCellStyle());
        CellUtil.mergeRegion(
                sheet,
                rankAreaRow + 1, rankAreaRow + 1,
                Constant.RANK_AREA_COL + 1, Constant.RANK_AREA_COL + 2
        );

        CellUtil.initCellValue(
                sheet, rankAreaRow + 1, Constant.RANK_AREA_COL + 3, "人数"
        ).setCellStyle(getDataTitleCellStyle());
    }

    private static void initDomesticAreaTitle(XSSFSheet sheet) {
        CellUtil.initCellValue(
                sheet, Constant.DOMESTIC_AREA_ROW, Constant.DOMESTIC_AREA_COL, "国内机场数据"
        ).setCellStyle(createDomesticAreaTitleCellStyle());
        CellUtil.mergeRegion(
                sheet,
                Constant.DOMESTIC_AREA_ROW, Constant.DOMESTIC_AREA_ROW,
                Constant.DOMESTIC_AREA_COL, Constant.DOMESTIC_AREA_COL + Constant.DOMESTIC_AREA_WIDTH - 1
        );

        CellUtil.initCellValue(
                sheet, Constant.DOMESTIC_AREA_ROW + 1, Constant.DOMESTIC_AREA_COL, ""
        ).setCellStyle(getDataTitleCellStyle());

        CellUtil.initCellValue(
                sheet, Constant.DOMESTIC_AREA_ROW + 1, Constant.DOMESTIC_AREA_COL + 1, TransferTypeEnum.DD.name()
        ).setCellStyle(getDataTitleCellStyle());
        CellUtil.mergeRegion(
                sheet,
                Constant.DOMESTIC_AREA_ROW + 1, Constant.DOMESTIC_AREA_ROW + 1,
                Constant.DOMESTIC_AREA_COL + 1, Constant.DOMESTIC_AREA_COL + 4
        );

        CellUtil.initCellValue(
                sheet, Constant.DOMESTIC_AREA_ROW + 1, Constant.DOMESTIC_AREA_COL + 5, TransferTypeEnum.DI.name()
        ).setCellStyle(getDataTitleCellStyle());
        CellUtil.mergeRegion(
                sheet,
                Constant.DOMESTIC_AREA_ROW + 1, Constant.DOMESTIC_AREA_ROW + 1,
                Constant.DOMESTIC_AREA_COL + 5, Constant.DOMESTIC_AREA_COL + 8
        );

        CellUtil.initCellValue(
                sheet, Constant.DOMESTIC_AREA_ROW + 2, Constant.DOMESTIC_AREA_COL, ""
        ).setCellStyle(getDataTitleCellStyle());
        initCalcDataTitleWithTotal(sheet, Constant.DOMESTIC_AREA_ROW + 2, Constant.DOMESTIC_AREA_COL + 1);
        initCalcDataTitleWithTotal(sheet, Constant.DOMESTIC_AREA_ROW + 2, Constant.DOMESTIC_AREA_COL + 5);
    }

    private static void initInternationalAreaTitle(XSSFSheet sheet) {
        CellUtil.initCellValue(
                sheet, Constant.INTERNATIONAL_AREA_ROW, Constant.INTERNATIONAL_AREA_COL, "国际机场数据"
        ).setCellStyle(createInternationalTitleAreaCellStyle());
        CellUtil.mergeRegion(
                sheet,
                Constant.INTERNATIONAL_AREA_ROW, Constant.INTERNATIONAL_AREA_ROW,
                Constant.INTERNATIONAL_AREA_COL, Constant.INTERNATIONAL_AREA_COL + Constant.INTERNATIONAL_AREA_WIDTH - 1
        );

        CellUtil.initCellValue(
                sheet, Constant.INTERNATIONAL_AREA_ROW + 1, Constant.INTERNATIONAL_AREA_COL, ""
        ).setCellStyle(getDataTitleCellStyle());

        CellUtil.initCellValue(
                sheet,
                Constant.INTERNATIONAL_AREA_ROW + 1, Constant.INTERNATIONAL_AREA_COL + 1,
                TransferTypeEnum.ID.name()
        ).setCellStyle(getDataTitleCellStyle());
        CellUtil.mergeRegion(
                sheet,
                Constant.INTERNATIONAL_AREA_ROW + 1, Constant.INTERNATIONAL_AREA_ROW + 1,
                Constant.INTERNATIONAL_AREA_COL + 1, Constant.INTERNATIONAL_AREA_COL + 4
        );

        CellUtil.initCellValue(
                sheet,
                Constant.INTERNATIONAL_AREA_ROW + 1, Constant.INTERNATIONAL_AREA_COL + 5,
                TransferTypeEnum.II.name()
        ).setCellStyle(getDataTitleCellStyle());
        CellUtil.mergeRegion(
                sheet,
                Constant.INTERNATIONAL_AREA_ROW + 1, Constant.INTERNATIONAL_AREA_ROW + 1,
                Constant.INTERNATIONAL_AREA_COL + 5, Constant.INTERNATIONAL_AREA_COL + 8
        );

        CellUtil.initCellValue(
                sheet, Constant.INTERNATIONAL_AREA_ROW + 2, Constant.INTERNATIONAL_AREA_COL, ""
        ).setCellStyle(getDataTitleCellStyle());
        initCalcDataTitleWithTotal(sheet, Constant.INTERNATIONAL_AREA_ROW + 2, Constant.INTERNATIONAL_AREA_COL + 1);
        initCalcDataTitleWithTotal(sheet, Constant.INTERNATIONAL_AREA_ROW + 2, Constant.INTERNATIONAL_AREA_COL + 5);

    }

    private static void initUnionAreaTitle(XSSFSheet sheet) {
        CellUtil.initCellValue(
                sheet, Constant.UNION_AREA_ROW, Constant.UNION_AREA_COL, "联盟内中转数据统计"
        ).setCellStyle(createUnionAreaTitleCellStyle());
        CellUtil.mergeRegion(
                sheet,
                Constant.UNION_AREA_ROW, Constant.UNION_AREA_ROW,
                Constant.UNION_AREA_COL, Constant.UNION_AREA_COL + Constant.UNION_AREA_WIDTH - 1
        );

        CellUtil.initCellValue(
                sheet, Constant.UNION_AREA_ROW + 1, Constant.UNION_AREA_COL, ""
        ).setCellStyle(getDataTitleCellStyle());

        CellUtil.initCellValue(
                sheet, Constant.UNION_AREA_ROW + 1, Constant.UNION_AREA_COL + 1, TransferTypeEnum.DD.name()
        ).setCellStyle(getDataTitleCellStyle());
        CellUtil.mergeRegion(
                sheet,
                Constant.UNION_AREA_ROW + 1, Constant.UNION_AREA_ROW + 1,
                Constant.UNION_AREA_COL + 1, Constant.UNION_AREA_COL + 3
        );

        CellUtil.initCellValue(
                sheet, Constant.UNION_AREA_ROW + 1, Constant.UNION_AREA_COL + 4, TransferTypeEnum.DI.name()
        ).setCellStyle(getDataTitleCellStyle());
        CellUtil.mergeRegion(
                sheet,
                Constant.UNION_AREA_ROW + 1, Constant.UNION_AREA_ROW + 1,
                Constant.UNION_AREA_COL + 4, Constant.UNION_AREA_COL + 6
        );

        CellUtil.initCellValue(
                sheet, Constant.UNION_AREA_ROW + 1, Constant.UNION_AREA_COL + 7, TransferTypeEnum.ID.name()
        ).setCellStyle(getDataTitleCellStyle());
        CellUtil.mergeRegion(
                sheet,
                Constant.UNION_AREA_ROW + 1, Constant.UNION_AREA_ROW + 1,
                Constant.UNION_AREA_COL + 7, Constant.UNION_AREA_COL + 9
        );

        CellUtil.initCellValue(
                sheet, Constant.UNION_AREA_ROW + 1, Constant.UNION_AREA_COL + 10, TransferTypeEnum.II.name()
        ).setCellStyle(getDataTitleCellStyle());
        CellUtil.mergeRegion(
                sheet,
                Constant.UNION_AREA_ROW + 1, Constant.UNION_AREA_ROW + 1,
                Constant.UNION_AREA_COL + 10, Constant.UNION_AREA_COL + 12
        );

        CellUtil.initCellValue(
                sheet, Constant.UNION_AREA_ROW + 2, Constant.UNION_AREA_COL, ""
        ).setCellStyle(getDataTitleCellStyle());
        initCalcDataTitle(sheet, Constant.UNION_AREA_ROW + 2, Constant.UNION_AREA_COL + 1);
        initCalcDataTitle(sheet, Constant.UNION_AREA_ROW + 2, Constant.UNION_AREA_COL + 4);
        initCalcDataTitle(sheet, Constant.UNION_AREA_ROW + 2, Constant.UNION_AREA_COL + 7);
        initCalcDataTitle(sheet, Constant.UNION_AREA_ROW + 2, Constant.UNION_AREA_COL + 10);
    }

    private static void initCalcDataTitle(XSSFSheet sheet, int rowNum, int columnNum) {
        CellUtil.initCellValue(sheet, rowNum, columnNum, "NTC").setCellStyle(getDataTitleCellStyle());
        CellUtil.initCellValue(sheet, rowNum, columnNum + 1, "TC").setCellStyle(getDataTitleCellStyle());
        CellUtil.initCellValue(sheet, rowNum, columnNum + 2, "办理率").setCellStyle(getDataTitleCellStyle());
    }

    private static void initCalcDataTitleWithTotal(XSSFSheet sheet, int rowNum, int columnNum) {
        CellUtil.initCellValue(sheet, rowNum, columnNum, "NTC").setCellStyle(getDataTitleCellStyle());
        CellUtil.initCellValue(sheet, rowNum, columnNum + 1, "TC").setCellStyle(getDataTitleCellStyle());
        CellUtil.initCellValue(sheet, rowNum, columnNum + 2, "办理率").setCellStyle(getDataTitleCellStyle());
        CellUtil.initCellValue(sheet, rowNum, columnNum + 3, "总数").setCellStyle(getDataTitleCellStyle());
    }

    private static void processTotalArea(XSSFSheet sheet, ExtractedData extractedData) {
        AtomicInteger rowNum = new AtomicInteger(Constant.TOTAL_AREA_ROW + 2);
        List<StatisticsResult> totalStaticalResultList = getTotalStatisticalResultList(extractedData.getTransferList());
        totalStaticalResultList.forEach(
                result -> processTotalAreaData(sheet, rowNum.getAndIncrement(), result)
        );
    }

    private static List<StatisticsResult> getTotalStatisticalResultList(List<Transfer> transferList) {
        List<StatisticsResult> statisticalResultList = getStatisticalResultList(transferList);

        int totalNtc = statisticalResultList.stream().mapToInt(StatisticsResult::getNtc).sum();
        int totalTc = statisticalResultList.stream().mapToInt(StatisticsResult::getTc).sum();

        Map<TransferTypeEnum, StatisticsResult> resultMap = statisticalResultList.stream()
                .collect(Collectors.toMap(StatisticsResult::getTransferType, Function.identity(), (o1, o2) -> o1));
        return Lists.newArrayList(
                getStatisticalResultOrDefault(resultMap, TransferTypeEnum.DD),
                getStatisticalResultOrDefault(resultMap, TransferTypeEnum.DI),
                getStatisticalResultOrDefault(resultMap, TransferTypeEnum.ID),
                getStatisticalResultOrDefault(resultMap, TransferTypeEnum.II),
                new StatisticsResult(
                        "总数", null, totalNtc, totalTc, CalcUtil.calculateRate(totalNtc, totalTc)
                )
        );
    }

    private static StatisticsResult getStatisticalResultOrDefault(Map<TransferTypeEnum, StatisticsResult> resultMap,
                                                                  TransferTypeEnum transferType) {
        return resultMap.getOrDefault(
                transferType, new StatisticsResult(transferType.name(), transferType, 0, 0, BigDecimal.ZERO)
        );
    }

    private static void processTotalAreaData(XSSFSheet sheet, int rowNum, StatisticsResult result) {
        CellUtil.initCellValue(sheet, rowNum, 0, result.getStatisticalUnit()).setCellStyle(getDataCellStyle());
        CellUtil.initCellValue(sheet, rowNum, 1, (double) result.getNtc()).setCellStyle(getDataCellStyle());
        CellUtil.initCellValue(sheet, rowNum, 2, (double) result.getTc()).setCellStyle(getDataCellStyle());
        CellUtil.initCellValue(sheet, rowNum, 3, result.getRate().doubleValue())
                .setCellStyle(getPercentDataCellStyle());
    }

    private static void processRankArea(XSSFSheet sheet, List<Transfer> transferList) {
        transferList.stream()
                .filter(Objects::nonNull)
                .collect(Collectors.groupingBy(Transfer::getTransferType))
                .forEach((key, value) -> {
                    AtomicInteger rowNum = new AtomicInteger(Constant.RANK_AREA_ROW_MAP.get(key.name()) + 2);
                    AtomicInteger rank = new AtomicInteger(1);
                    value.stream()
                            .map(transfer -> new MutablePair<>(transfer.getRouteName(), transfer.getTotalTc()))
                            .collect(Collectors.groupingBy(Pair::getLeft))
                            .entrySet()
                            .stream()
                            .map(e ->
                                    new MutablePair<>(
                                            e.getKey(),
                                            e.getValue().stream().mapToInt(MutablePair::getRight).sum()
                                    )
                            )
                            .filter(pair -> pair.getRight() > 0)
                            .sorted((p1, p2) -> -p1.getRight().compareTo(p2.getRight()))
                            .limit(10)
                            .map(pair -> new RankResult(pair.getLeft(), pair.getRight(), rank.getAndIncrement()))
                            .forEach(rankResult -> processRankData(sheet, rowNum.getAndIncrement(), rankResult));
                });
    }

    private static void processRankData(XSSFSheet sheet, int rowNum, RankResult rankResult) {
        Optional<RankResult> input = Optional.of(rankResult);

        CellUtil.initCellValue(
                sheet, rowNum, Constant.RANK_AREA_COL,
                (double) input.map(RankResult::getRank).orElse(0)
        ).setCellStyle(getRankCellStyle());

        CellUtil.initCellValue(
                sheet, rowNum, Constant.RANK_AREA_COL + 1,
                input.map(RankResult::getRouteName).filter(StringUtils::isNotBlank).orElse("")
        ).setCellStyle(getRankCellStyle());
        CellUtil.mergeRegion(sheet, rowNum, rowNum, Constant.RANK_AREA_COL + 1, Constant.RANK_AREA_COL + 2);

        CellUtil.initCellValue(
                sheet, rowNum, Constant.RANK_AREA_COL + 3,
                (double) input.map(RankResult::getTotalTc).orElse(0)
        ).setCellStyle(getRankCellStyle());
    }

    private static void processAirportArea(XSSFSheet sheet, List<Transfer> transferList) {
        AtomicInteger domesticRowNum = new AtomicInteger(Constant.DOMESTIC_AREA_ROW + 3);
        AtomicInteger internationalRowNum = new AtomicInteger(Constant.INTERNATIONAL_AREA_ROW + 3);

        transferList.stream()
                .collect(Collectors.groupingBy(Transfer::getSourceAirportCode))
                .entrySet()
                .stream()
                .sorted(Map.Entry.comparingByKey())
                .map(Main::getAirportStatisticalResult)
                .filter(Main::validAirportStatisticalResult)
                .forEach(airportResult -> {
                    if (Config.isDomesticAirport(airportResult.getAirportCode())) {
                        processDomesticAirportData(sheet, domesticRowNum.getAndIncrement(), airportResult);
                    } else {
                        processInternationalAirportData(sheet, internationalRowNum.getAndIncrement(), airportResult);
                    }
                });
    }

    private static AirportStatisticalResult getAirportStatisticalResult(Map.Entry<String, List<Transfer>> entry) {
        List<StatisticsResult> statisticalResultList = getStatisticalResultList(entry.getValue());

        AtomicReference<AirportTypeEnum> airportType = new AtomicReference<>();

        Map<AirportTypeEnum, StatisticsResult> collect = statisticalResultList.stream()
                .sorted()
                .filter(Objects::nonNull)
                .peek(result -> airportType.set(result.getTransferType().getSourceAirportType()))
                .collect(
                        Collectors.toMap(
                                result -> result.getTransferType().getTargetAirportType(),
                                Function.identity(),
                                (o1, o2) -> o1
                        )
                );

        return new AirportStatisticalResult(
                entry.getKey(), airportType.get(),
                collect.get(AirportTypeEnum.DOMESTIC), collect.get(AirportTypeEnum.INTERNATIONAL)
        );
    }

    private static boolean validAirportStatisticalResult(AirportStatisticalResult airportResult) {
        return validStatisticalResult(airportResult.getDomesticTransferStatisticalResult())
                || validStatisticalResult(airportResult.getInternationalTransferStatisticalResult());
    }

    private static void processDomesticAirportData(XSSFSheet sheet, int rowNum, AirportStatisticalResult result) {
        Optional<AirportStatisticalResult> input = Optional.of(result);

        CellUtil.initCellValue(
                sheet, rowNum, Constant.DOMESTIC_AREA_COL,
                input.map(AirportStatisticalResult::getAirportCode)
                        .filter(StringUtils::isNotBlank)
                        .orElseThrow(() -> new RuntimeException("机场编码错误!"))
        ).setCellStyle(getDataCellStyle());

        CellUtil.initCellValue(
                sheet, rowNum, Constant.DOMESTIC_AREA_COL + 1,
                (double) input.map(AirportStatisticalResult::getDomesticTransferStatisticalResult)
                        .map(StatisticsResult::getNtc).orElse(0)
        ).setCellStyle(getDataCellStyle());

        CellUtil.initCellValue(
                sheet, rowNum, Constant.DOMESTIC_AREA_COL + 2,
                (double) input.map(AirportStatisticalResult::getDomesticTransferStatisticalResult)
                        .map(StatisticsResult::getTc).orElse(0)
        ).setCellStyle(getDataCellStyle());

        CellUtil.initCellValue(
                sheet, rowNum, Constant.DOMESTIC_AREA_COL + 3,
                input.map(AirportStatisticalResult::getDomesticTransferStatisticalResult)
                        .map(StatisticsResult::getRate)
                        .map(BigDecimal::doubleValue)
                        .orElse(0.0D)
        ).setCellStyle(getPercentDataCellStyle());

        CellUtil.initCellValue(
                sheet, rowNum, Constant.DOMESTIC_AREA_COL + 4,
                (double) input.map(AirportStatisticalResult::getDomesticTransferStatisticalResult)
                        .map(StatisticsResult::getTotal).orElse(0)
        ).setCellStyle(getDataCellStyle());

        CellUtil.initCellValue(
                sheet, rowNum, Constant.DOMESTIC_AREA_COL + 5,
                (double) input.map(AirportStatisticalResult::getInternationalTransferStatisticalResult)
                        .map(StatisticsResult::getNtc).orElse(0)
        ).setCellStyle(getDataCellStyle());

        CellUtil.initCellValue(
                sheet, rowNum, Constant.DOMESTIC_AREA_COL + 6,
                (double) input.map(AirportStatisticalResult::getInternationalTransferStatisticalResult)
                        .map(StatisticsResult::getTc).orElse(0)
        ).setCellStyle(getDataCellStyle());

        CellUtil.initCellValue(
                sheet, rowNum, Constant.DOMESTIC_AREA_COL + 7,
                input.map(AirportStatisticalResult::getInternationalTransferStatisticalResult)
                        .map(StatisticsResult::getRate)
                        .map(BigDecimal::doubleValue)
                        .orElse(0.0D)
        ).setCellStyle(getPercentDataCellStyle());

        CellUtil.initCellValue(
                sheet, rowNum, Constant.DOMESTIC_AREA_COL + 8,
                (double) input.map(AirportStatisticalResult::getInternationalTransferStatisticalResult)
                        .map(StatisticsResult::getTotal).orElse(0)
        ).setCellStyle(getDataCellStyle());

    }

    private static void processInternationalAirportData(XSSFSheet sheet, int rowNum, AirportStatisticalResult result) {
        Optional<AirportStatisticalResult> input = Optional.of(result);

        CellUtil.initCellValue(
                sheet, rowNum, Constant.INTERNATIONAL_AREA_COL,
                input.map(AirportStatisticalResult::getAirportCode)
                        .filter(StringUtils::isNotBlank)
                        .orElseThrow(() -> new RuntimeException("机场编码错误!"))
        ).setCellStyle(getDataCellStyle());

        CellUtil.initCellValue(
                sheet, rowNum, Constant.INTERNATIONAL_AREA_COL + 1,
                (double) input.map(AirportStatisticalResult::getDomesticTransferStatisticalResult)
                        .map(StatisticsResult::getNtc).orElse(0)
        ).setCellStyle(getDataCellStyle());

        CellUtil.initCellValue(
                sheet, rowNum, Constant.INTERNATIONAL_AREA_COL + 2,
                (double) input.map(AirportStatisticalResult::getDomesticTransferStatisticalResult)
                        .map(StatisticsResult::getTc).orElse(0)
        ).setCellStyle(getDataCellStyle());

        CellUtil.initCellValue(
                sheet, rowNum, Constant.INTERNATIONAL_AREA_COL + 3,
                input.map(AirportStatisticalResult::getDomesticTransferStatisticalResult)
                        .map(StatisticsResult::getRate)
                        .map(BigDecimal::doubleValue)
                        .orElse(0.0D)
        ).setCellStyle(getPercentDataCellStyle());

        CellUtil.initCellValue(
                sheet, rowNum, Constant.INTERNATIONAL_AREA_COL + 4,
                (double) input.map(AirportStatisticalResult::getDomesticTransferStatisticalResult)
                        .map(StatisticsResult::getTotal).orElse(0)
        ).setCellStyle(getDataCellStyle());

        CellUtil.initCellValue(
                sheet, rowNum, Constant.INTERNATIONAL_AREA_COL + 5,
                (double) input.map(AirportStatisticalResult::getInternationalTransferStatisticalResult)
                        .map(StatisticsResult::getNtc).orElse(0)
        ).setCellStyle(getDataCellStyle());

        CellUtil.initCellValue(
                sheet, rowNum, Constant.INTERNATIONAL_AREA_COL + 6,
                (double) input.map(AirportStatisticalResult::getInternationalTransferStatisticalResult)
                        .map(StatisticsResult::getTc).orElse(0)
        ).setCellStyle(getDataCellStyle());

        CellUtil.initCellValue(
                sheet, rowNum, Constant.INTERNATIONAL_AREA_COL + 7,
                input.map(AirportStatisticalResult::getInternationalTransferStatisticalResult)
                        .map(StatisticsResult::getRate)
                        .map(BigDecimal::doubleValue)
                        .orElse(0.0D)
        ).setCellStyle(getPercentDataCellStyle());

        CellUtil.initCellValue(
                sheet, rowNum, Constant.INTERNATIONAL_AREA_COL + 8,
                (double) input.map(AirportStatisticalResult::getInternationalTransferStatisticalResult)
                        .map(StatisticsResult::getTotal).orElse(0)
        ).setCellStyle(getDataCellStyle());

    }

    private static void processUnionArea(XSSFSheet sheet, List<Transfer> transferList) {
        AtomicInteger unionRowNum = new AtomicInteger(Constant.UNION_AREA_ROW + 3);
        transferList.stream()
                .filter(Objects::nonNull)
                .filter(transfer -> Config.isUnionFlight(transfer.getUnionCode()))
                .collect(Collectors.groupingBy(Transfer::getUnionCode))
                .entrySet()
                .stream()
                .map(Main::getUnionStatisticalResult)
                .filter(Main::validUnionStatisticalResult)
                .forEach(result -> processUnionAreaData(sheet, unionRowNum.getAndIncrement(), result));
    }

    private static UnionStatisticalResult getUnionStatisticalResult(Map.Entry<String, List<Transfer>> entry) {
        Map<TransferTypeEnum, StatisticsResult> resultMap =
                getStatisticalResultList(entry.getValue()).stream()
                        .collect(
                                Collectors.toMap(
                                        StatisticsResult::getTransferType,
                                        Function.identity(),
                                        (o1, o2) -> o1)
                        );
        return new UnionStatisticalResult(
                entry.getKey(),
                resultMap.get(TransferTypeEnum.DD),
                resultMap.get(TransferTypeEnum.DI),
                resultMap.get(TransferTypeEnum.ID),
                resultMap.get(TransferTypeEnum.II)
        );
    }

    private static void processUnionAreaData(XSSFSheet sheet, int rowNum, UnionStatisticalResult result) {
        Optional<UnionStatisticalResult> input = Optional.of(result);

        CellUtil.initCellValue(
                sheet, rowNum, Constant.UNION_AREA_COL,
                input.map(UnionStatisticalResult::getUnionCode)
                        .filter(StringUtils::isNotBlank)
                        .orElseThrow(() -> new RuntimeException("联盟编码错误!"))
        ).setCellStyle(getDataCellStyle());

        CellUtil.initCellValue(
                sheet, rowNum, Constant.UNION_AREA_COL + 1,
                (double) input.map(UnionStatisticalResult::getDdTransferStatisticalResult)
                        .map(StatisticsResult::getNtc).orElse(0)
        ).setCellStyle(getDataCellStyle());

        CellUtil.initCellValue(
                sheet, rowNum, Constant.UNION_AREA_COL + 2,
                (double) input.map(UnionStatisticalResult::getDdTransferStatisticalResult)
                        .map(StatisticsResult::getTc).orElse(0)
        ).setCellStyle(getDataCellStyle());

        CellUtil.initCellValue(
                sheet, rowNum, Constant.UNION_AREA_COL + 3,
                input.map(UnionStatisticalResult::getDdTransferStatisticalResult)
                        .map(StatisticsResult::getRate)
                        .map(BigDecimal::doubleValue)
                        .orElse(0.0D)
        ).setCellStyle(getPercentDataCellStyle());

        CellUtil.initCellValue(
                sheet, rowNum, Constant.UNION_AREA_COL + 4,
                (double) input.map(UnionStatisticalResult::getDiTransferStatisticalResult)
                        .map(StatisticsResult::getNtc).orElse(0)
        ).setCellStyle(getDataCellStyle());

        CellUtil.initCellValue(
                sheet, rowNum, Constant.UNION_AREA_COL + 5,
                (double) input.map(UnionStatisticalResult::getDiTransferStatisticalResult)
                        .map(StatisticsResult::getTc).orElse(0)
        ).setCellStyle(getDataCellStyle());

        CellUtil.initCellValue(
                sheet, rowNum, Constant.UNION_AREA_COL + 6,
                input.map(UnionStatisticalResult::getDiTransferStatisticalResult)
                        .map(StatisticsResult::getRate)
                        .map(BigDecimal::doubleValue)
                        .orElse(0.0D)
        ).setCellStyle(getPercentDataCellStyle());

        CellUtil.initCellValue(
                sheet, rowNum, Constant.UNION_AREA_COL + 7,
                (double) input.map(UnionStatisticalResult::getIdTransferStatisticalResult)
                        .map(StatisticsResult::getNtc).orElse(0)
        ).setCellStyle(getDataCellStyle());

        CellUtil.initCellValue(
                sheet, rowNum, Constant.UNION_AREA_COL + 8,
                (double) input.map(UnionStatisticalResult::getIdTransferStatisticalResult)
                        .map(StatisticsResult::getTc).orElse(0)
        ).setCellStyle(getDataCellStyle());

        CellUtil.initCellValue(
                sheet, rowNum, Constant.UNION_AREA_COL + 9,
                input.map(UnionStatisticalResult::getIdTransferStatisticalResult)
                        .map(StatisticsResult::getRate)
                        .map(BigDecimal::doubleValue)
                        .orElse(0.0D)
        ).setCellStyle(getPercentDataCellStyle());

        CellUtil.initCellValue(
                sheet, rowNum, Constant.UNION_AREA_COL + 10,
                (double) input.map(UnionStatisticalResult::getIiTransferStatisticalResult)
                        .map(StatisticsResult::getNtc).orElse(0)
        ).setCellStyle(getDataCellStyle());

        CellUtil.initCellValue(
                sheet, rowNum, Constant.UNION_AREA_COL + 11,
                (double) input.map(UnionStatisticalResult::getIiTransferStatisticalResult)
                        .map(StatisticsResult::getTc).orElse(0)
        ).setCellStyle(getDataCellStyle());

        CellUtil.initCellValue(
                sheet, rowNum, Constant.UNION_AREA_COL + 12,
                input.map(UnionStatisticalResult::getIiTransferStatisticalResult)
                        .map(StatisticsResult::getRate)
                        .map(BigDecimal::doubleValue)
                        .orElse(0.0D)
        ).setCellStyle(getPercentDataCellStyle());
    }

    private static boolean validUnionStatisticalResult(UnionStatisticalResult result) {
        return validStatisticalResult(result.getDdTransferStatisticalResult())
                || validStatisticalResult(result.getDiTransferStatisticalResult())
                || validStatisticalResult(result.getIdTransferStatisticalResult())
                || validStatisticalResult(result.getIiTransferStatisticalResult());
    }

    private static boolean validStatisticalResult(StatisticsResult result) {
        return result != null && (result.getNtc() != 0 || result.getTc() != 0);
    }

    private static List<StatisticsResult> getStatisticalResultList(List<Transfer> transferList) {
        return transferList.stream()
                .collect(Collectors.groupingBy(Transfer::getTransferType))
                .entrySet()
                .stream()
                .map(entry -> {
                    List<Transfer> transferSubList = entry.getValue();
                    int ntc = transferSubList.stream().mapToInt(Transfer::getTotalNtc).sum();
                    int tc = transferSubList.stream().mapToInt(Transfer::getTotalTc).sum();
                    return new StatisticsResult(
                            entry.getKey().name(), entry.getKey(), ntc, tc, CalcUtil.calculateRate(ntc, tc)
                    );
                })
                .filter(Main::validStatisticalResult)
                .sorted()
                .collect(Collectors.toList());
    }


    private static XSSFCellStyle createTitleCellStyle() {
        XSSFCellStyle cellStyle = createAlignedCellStyle();
        cellStyle.setFont(createTitleFont());
        return cellStyle;
    }

    private static XSSFCellStyle createTotalAreaTitleCellStyle() {
        XSSFCellStyle borderedCellStyle = createAreaTitleCellStyle();
        borderedCellStyle.setFillForegroundColor(new XSSFColor(new Color(34, 59, 97), new DefaultIndexedColorMap()));
        return borderedCellStyle;
    }

    private static XSSFCellStyle createRankAreaTitleCellStyle() {
        XSSFCellStyle borderedCellStyle = createAreaTitleCellStyle();
        borderedCellStyle.setFillForegroundColor(new XSSFColor(new Color(98, 0, 2), new DefaultIndexedColorMap()));
        return borderedCellStyle;
    }

    private static XSSFCellStyle createSignAreaTopCellStyle() {
        XSSFCellStyle borderedCellStyle = createBorderedCellStyle();
        borderedCellStyle.setBorderBottom(BorderStyle.NONE);
        borderedCellStyle.setFont(getDataFont());
        borderedCellStyle.setAlignment(HorizontalAlignment.LEFT);
        return borderedCellStyle;
    }

    private static XSSFCellStyle createSignAreaMiddleCellStyle() {
        XSSFCellStyle borderedCellStyle = createBorderedCellStyle();
        borderedCellStyle.setBorderTop(BorderStyle.NONE);
        borderedCellStyle.setBorderBottom(BorderStyle.NONE);
        borderedCellStyle.setFont(getDataFont());
        borderedCellStyle.setAlignment(HorizontalAlignment.LEFT);
        return borderedCellStyle;
    }

    private static XSSFCellStyle createSignAreaBottomCellStyle() {
        XSSFCellStyle borderedCellStyle = createBorderedCellStyle();
        borderedCellStyle.setBorderTop(BorderStyle.NONE);
        borderedCellStyle.setFont(getDataFont());
        borderedCellStyle.setAlignment(HorizontalAlignment.LEFT);
        return borderedCellStyle;
    }

    private static XSSFCellStyle createDomesticAreaTitleCellStyle() {
        XSSFCellStyle borderedCellStyle = createAreaTitleCellStyle();
        borderedCellStyle.setFillForegroundColor(new XSSFColor(new Color(192, 163, 83), new DefaultIndexedColorMap()));
        return borderedCellStyle;
    }

    private static XSSFCellStyle createInternationalTitleAreaCellStyle() {
        XSSFCellStyle borderedCellStyle = createAreaTitleCellStyle();
        borderedCellStyle.setFillForegroundColor(new XSSFColor(new Color(181, 181, 182), new DefaultIndexedColorMap()));
        return borderedCellStyle;
    }

    private static XSSFCellStyle createUnionAreaTitleCellStyle() {
        XSSFCellStyle borderedCellStyle = createAreaTitleCellStyle();
        borderedCellStyle.setFillForegroundColor(new XSSFColor(new Color(42, 83, 144), new DefaultIndexedColorMap()));
        return borderedCellStyle;
    }

    private static XSSFCellStyle createAreaTitleCellStyle() {
        XSSFCellStyle borderedCellStyle = createBorderedCellStyle();
        borderedCellStyle.setFont(getAreaTitleFont());
        borderedCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return borderedCellStyle;
    }

    private static XSSFCellStyle createBorderedCellStyle() {
        XSSFCellStyle borderedCellStyle = createAlignedCellStyle();

        borderedCellStyle.setBorderLeft(BorderStyle.THIN);
        borderedCellStyle.setBorderRight(BorderStyle.THIN);
        borderedCellStyle.setBorderBottom(BorderStyle.THIN);
        borderedCellStyle.setBorderTop(BorderStyle.THIN);
        return borderedCellStyle;
    }

    private static XSSFCellStyle createAlignedCellStyle() {
        XSSFCellStyle cellStyle = WORKBOOK.createCellStyle();

        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return cellStyle;
    }

    private static XSSFCellStyle DATA_TITLE_CELL_STYLE = null;

    private static XSSFCellStyle getDataTitleCellStyle() {
        if (DATA_TITLE_CELL_STYLE == null) {
            DATA_TITLE_CELL_STYLE = createAlignedCellStyle();
            DATA_TITLE_CELL_STYLE.setFont(getDataFont());
        }
        return DATA_TITLE_CELL_STYLE;
    }

    private static XSSFCellStyle RANK_CELL_STYLE = null;

    private static XSSFCellStyle getRankCellStyle() {
        if (RANK_CELL_STYLE == null) {
            RANK_CELL_STYLE = createAlignedCellStyle();
            RANK_CELL_STYLE.setFont(getDataFont());
        }
        return RANK_CELL_STYLE;
    }

    private static XSSFCellStyle DATA_CELL_STYLE = null;

    private static XSSFCellStyle getDataCellStyle() {
        if (DATA_CELL_STYLE == null) {
            DATA_CELL_STYLE = WORKBOOK.createCellStyle();
            DATA_CELL_STYLE.setFont(getDataFont());
        }
        return DATA_CELL_STYLE;
    }

    private static XSSFCellStyle PERCENT_DATA_CELL_STYLE = null;

    private static XSSFCellStyle getPercentDataCellStyle() {
        if (PERCENT_DATA_CELL_STYLE == null) {
            PERCENT_DATA_CELL_STYLE = WORKBOOK.createCellStyle();
            PERCENT_DATA_CELL_STYLE.setFont(getDataFont());
            XSSFDataFormat percentDataFormat = WORKBOOK.createDataFormat();
            PERCENT_DATA_CELL_STYLE.setDataFormat(percentDataFormat.getFormat("0.00%"));
        }
        return PERCENT_DATA_CELL_STYLE;
    }

    private static XSSFFont createTitleFont() {
        XSSFFont titleFont = WORKBOOK.createFont();
        titleFont.setFontName("黑体");
        titleFont.setFontHeightInPoints((short) 24);
        titleFont.setBold(true);
        return titleFont;
    }

    private static XSSFFont DATA_FONT = null;

    private static XSSFFont getDataFont() {
        if (DATA_FONT == null) {
            DATA_FONT = WORKBOOK.createFont();
            DATA_FONT.setFontName("等线");
            DATA_FONT.setFontHeightInPoints((short) 11);
        }
        return DATA_FONT;
    }

    private static XSSFFont AREA_TITLE_FONT = null;

    private static XSSFFont getAreaTitleFont() {
        if (AREA_TITLE_FONT == null) {
            AREA_TITLE_FONT = WORKBOOK.createFont();
            AREA_TITLE_FONT.setFontName("微软黑体");
            AREA_TITLE_FONT.setFontHeightInPoints((short) 11);
            AREA_TITLE_FONT.setBold(true);
            AREA_TITLE_FONT.setColor(new XSSFColor(new Color(255, 255, 255), new DefaultIndexedColorMap()));
        }
        return AREA_TITLE_FONT;
    }


}
